<?php

class SiteCostCenter extends AppModel {

    var $name = 'SiteCostCenter';
    var $order = 'SiteCostCenter.name';
    var $belongsTo = array(
        'User' => array(
            'foreignKey' => 'collector_id',
            'fields' => array('name')
        ), 'Site'
    );
    var $validate = array(
        'name' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                'message' => 'Cost center name is required.',
                'required' => true,
            ),
        ),
        'site_id' => array(
            'notEmpty' => array(
                'rule' => array('notEmpty'),
                'message' => 'Site is required.',
                'required' => true,
            ),
        )
    );

    function reportStockOutByCostCenter($site_id, $conditions, $display_stock_out) {
        $sql = "SELECT SiteCostCenter.id, SiteCostCenter.name,
                       CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from,
                       CustStockOutHead.id, CustStockOutHead.stock_out_no, DATE_FORMAT(CustStockOutHead.stock_out_date, '%e/%c/%Y') stock_out_date,  CustStockOutHead.dispatch_from, CustStockOutHead.remark,
		       Product.part_no, Product.supplier_part_no, Supplier.name, Product.text, CustProductSite.text,
		       (CASE WHEN CustProductSite.text = '' OR CustProductSite.text IS NULL THEN Product.text ELSE CustProductSite.text END) text,
		       AVG(CustStockOut.stock_out_avg_price) price, SUM(stock_out_qty) stock_out_qty, SUM(return_qty) return_qty, SUM(net_stock_out_qty) net_stock_out_qty, COUNT(*) frequency,
                       SUM(net_stock_out_qty * CustStockOut.stock_out_avg_price) amount
                FROM pre_cust_products_sites CustProductSite
                JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                JOIN pre_site_cost_centers SiteCostCenter ON(CustStockOutHead.site_cost_center_id = SiteCostCenter.id)
                WHERE CustProductSite.site_id = {$site_id}
                AND CustProductSite.discontinued = 0 ";

       // Check เงื่อนไขการค้นหา
        $sql .= $this->addSqlConditions($conditions);
       
        $sql .= " GROUP BY SiteCostCenter.name, CustProductSite.stock_code " . ($display_stock_out ? ", CustStockOutHead.stock_out_no " : '') . " WITH ROLLUP ";
        $result = $this->query($sql);

        $data = null;
        $cps = array();
        $csoh = array();
        $i = $j = $k = 0;
        // query ได้ data เรียงจาก group ย่อยสุด มาหา group ใหญ่สุด
        // CustStockOutHead <= CustProductSite <= SiteCostCenter
        // แปลง result ให้เหมือน $this->data ปกติ
        foreach ($result as $row) {
            // row stock out head
            if ($display_stock_out && !empty($row['CustStockOutHead']['stock_out_no'])) {
                $csoh['CustStockOutHead'][$k] = array_merge($row['CustStockOutHead'], $row[0]);
                ++$k;
            } elseif (!empty($row['CustProductSite']['stock_code'])) {
                // row stock code
                $cps['CustProductSite'][$j]['CustProductSite'] = $row['CustProductSite'];
                $cps['CustProductSite'][$j]['Product'] = $row['Product'];
                $cps['CustProductSite'][$j]['Supplier'] = $row['Supplier'];
                $cps['CustProductSite'][$j]['CustStockOutHead'][0] = $row['CustStockOutHead'];
                $cps['CustProductSite'][$j][] = $row[0];
                if ($display_stock_out) {
                    $cps['CustProductSite'][$j] = array_merge($cps['CustProductSite'][$j], $csoh);
                    $csoh = array();
                }
                $k = 0;
                ++$j;
            } elseif (!empty($row['SiteCostCenter']['name']) || !empty($row[0]['name'])) {
                // row site cost center
                $data[$i]['SiteCostCenter'] = array_merge($row['SiteCostCenter'], $row[0]);
                $data[$i] = array_merge($data[$i], $cps);
                $cps = array();
                ++$i;
                $j = 0;
            }
        }
        return $data;
    }


    function reportSupplierCustStockOutByCostCenter($supplier_id, $conditions, $display_stock_out) {

        $sql = "SELECT SiteCostCenter.id, SiteCostCenter.name, Customer.name, Site.name,
                       CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from,
                       CustStockOutHead.id, CustStockOutHead.stock_out_no, DATE_FORMAT(CustStockOutHead.stock_out_date, '%e/%c/%Y') stock_out_date,  CustStockOutHead.dispatch_from, CustStockOutHead.remark,
		       Product.part_no, Product.supplier_part_no, Supplier.name, Product.text, CustProductSite.text,
		       (CASE WHEN CustProductSite.text = '' OR CustProductSite.text IS NULL THEN Product.text ELSE CustProductSite.text END) text,
		       AVG(CustStockOut.stock_out_avg_price) price, SUM(stock_out_qty) stock_out_qty, SUM(return_qty) return_qty, SUM(net_stock_out_qty) net_stock_out_qty, COUNT(*) frequency,
                       SUM(net_stock_out_qty * CustStockOut.stock_out_avg_price) amount
                FROM pre_cust_products_sites CustProductSite
                JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                JOIN pre_site_cost_centers SiteCostCenter ON(CustStockOutHead.site_cost_center_id = SiteCostCenter.id)
                JOIN pre_customers Customer ON (CustStockOutHead.customer_id = Customer.id)
                JOIN pre_sites Site ON (CustStockOutHead.site_id = Site.id)
                WHERE CustStockOutHead.supplier_id = {$supplier_id}
                AND CustStockOutHead.dispatch_from = 'supplier'
                AND CustProductSite.discontinued = 0 ";

        // Check เงื่อนไขการค้นหา
        $sql .= $this->addSqlConditions($conditions);

        $sql .= " GROUP BY SiteCostCenter.name, CustProductSite.stock_code " . ($display_stock_out ? ", CustStockOutHead.stock_out_no " : '') . " WITH ROLLUP ";
        $result = $this->query($sql);

        $data = null;
        $cps = array();
        $csoh = array();
        $i = $j = $k = 0;
        // query ได้ data เรียงจาก group ย่อยสุด มาหา group ใหญ่สุด
        // CustStockOutHead <= CustProductSite <= SiteCostCenter
        // แปลง result ให้เหมือน $this->data ปกติ
        foreach ($result as $row) {
            // row stock out head
            if ($display_stock_out && !empty($row['CustStockOutHead']['stock_out_no'])) {
                $csoh['CustStockOutHead'][$k] = array_merge($row['CustStockOutHead'], $row[0]);
                ++$k;
            } elseif (!empty($row['CustProductSite']['stock_code'])) {
                // row stock code
                $cps['CustProductSite'][$j]['CustProductSite'] = $row['CustProductSite'];
                $cps['CustProductSite'][$j]['Product'] = $row['Product'];
                $cps['CustProductSite'][$j]['Supplier'] = $row['Supplier'];
                $cps['CustProductSite'][$j]['CustStockOutHead'][0] = $row['CustStockOutHead'];
                $cps['CustProductSite'][$j][] = $row[0];
                if ($display_stock_out) {
                    $cps['CustProductSite'][$j] = array_merge($cps['CustProductSite'][$j], $csoh);
                    $csoh = array();
                }
                $k = 0;
                ++$j;
            } elseif (!empty($row['SiteCostCenter']['name']) || !empty($row[0]['name'])) {
                // row site cost center
                $data[$i]['SiteCostCenter'] = array_merge($row['SiteCostCenter'], $row[0]);
                $data[$i] = array_merge($data[$i], $cps);
                $cps = array();
                ++$i;
                $j = 0;
            }
        }
        return $data;
    }
}